<cfinclude template="/admin/application.cfm">

<cfquery name="getSurveyData" datasource="#datasourcename#">
	SELECT      *, WS_ORGANIZATION_WORKSHOPS.ORW_SITE AS WorkshopName, WS_ORGANIZATION_WORKSHOPS.ORW_COUNTY AS County, 
	                      WS_ORGANIZATION_WORKSHOPS.ORW_REGIONID AS RegionID, 
	                      WS_ORGANIZATION_WORKSHOP_DATES.OWD_STARTDATETIME AS WorkshopDate
	FROM         WSE_WORKSHOP_SURVEY_EVALUATIONS INNER JOIN
	                      WS_ORGANIZATION_WORKSHOP_DATES ON 
	                      WSE_WORKSHOP_SURVEY_EVALUATIONS.EVS_ORGANIZATIONWORKSHOPDATEID = WS_ORGANIZATION_WORKSHOP_DATES.OWD_ORGANIZATIONWORKSHOPDATEID
	                       INNER JOIN
	                      WS_ORGANIZATION_WORKSHOPS ON 
	                      WS_ORGANIZATION_WORKSHOP_DATES.OWD_ORGANIZATIONWORKSHOPID = WS_ORGANIZATION_WORKSHOPS.ORW_ORGANIZATIONWORKSHOPID
	ORDER BY WSE_WORKSHOP_SURVEY_EVALUATIONS.EVS_SURVEYDATE
</cfquery>

<cfif getSurveyData.RecordCount gt 0>
	<cfsetting enablecfoutputonly="Yes" showdebugoutput="No">
	<cfcontent type="text/x-csv">
	<cfheader name="content-disposition" value="attachment; filename=export-#dateformat(now(), "yyyymmdd")##timeformat(now(), "hhmmss")#.csv">
	<cfoutput>"Location ID","ID","ShortLongForm","ResearchIDNo","WorkshopName","WorkshopDate","Q1","Q2","Q3","Q4","Q5","Q6","Q7","Q8","Q9_1","Q9_2","Q9_3","Q9_4","Q9_4_Other_Text","Q10_1","Q10_2","Q10_3","Q10_4","Q11","Q12","Q13","Q14_1","Q14_2","Q14_3","Q14_4","Q14_5","Q14_5_Other_Text","Q15a","Q15b","Q16_1","Q16_2","Q16_3","Q16_4","Q16_5","Q16_6","Q16_7","Q16_8","Q16_9","Q16_10","Q16_11","Q16_12","Q16_13","Q16_14","Q16_14_Other_Text","Q17","Q18_1","Q18_2","Q18_3","Q18_4","Q18_5","Q18_6","Q18_6_Other_Text","Q19","Q20_1","Q20_2","Q20_3","Q20_4","Q20_5","Q20_6","Q20_6_Other_Text","Q21","Q22a","Q22b","Q22c","Q22d","First_Name","Last_Name","FirstAndLast_Name","Home_Phone","Cell_Phone","Home_Address","City","Zip_Code","E_mail","Date_Of_Birth","Last4SSN","AdditionalContact1FirstAndLast_Name","AdditionalContact1Email","AdditionalContact1Phone","AdditionalContact1Home_Address","AdditionalContact1City","AdditionalContact1Zip_Code","AdditionalContact2FirstAndLast_Name","AdditionalContact2Email","AdditionalContact2Phone","AdditionalContact2Home_Address","AdditionalContact2City","AdditionalContact2Zip_Code","OperatorNotes","County","Region ID","surveyDate"#chr(13)#</cfoutput>
	<cfset lastWSID = "">
	<cfset studentID = 0>
	<cfoutput query="getSurveyData"><cfif lastWSID neq EVS_ORGANIZATIONWORKSHOPDATEID><cfset studentID = 0><cfset lastWSId = EVS_ORGANIZATIONWORKSHOPDATEID></cfif><cfset studentId = studentId + 1>"#EVS_ORGANIZATIONWORKSHOPDATEID#","#numberformat(studentId, "000")#","","#evs_researchid#","#workshopName#","#dateformat(workshopDate, "m/d/yyyy")#","#EVS_Q1#",<cfswitch expression="#evs_q2#"><cfcase value="2">"3",</cfcase><cfcase value="3">"2",</cfcase><cfdefaultcase>"#evs_q1#",</cfdefaultcase></cfswitch>"#EVS_Q3#","#EVS_Q4#","#EVS_Q5#","#EVS_Q6#","#EVS_Q7#","#reReplace(EVS_Q8, '"', '""', "ALL")#",<cfloop index="i" from="1" to="4"><cfif listFind(EVS_Q9, i)>"#i#",<cfelse>"",</cfif></cfloop>"#reReplace(EVS_Q9_OTHER, '"', '""', "ALL")#",<cfloop index="i" from="1" to="4"><cfif listFind(EVS_Q10, i)>"#i#",<cfelse>"",</cfif></cfloop>"#EVS_Q11#","#EVS_Q12#","#EVS_Q13#",<cfloop index="i" from="1" to="5"><cfif listFind(EVS_Q14, i)>"#i#",<cfelse>"",</cfif></cfloop>"#reReplace(EVS_Q14_OTHER, '"', '""', "ALL")#","#EVS_Q15#","#reReplace(EVS_Q15_OTHER, '"', '""', "ALL")#",<cfset crossList = "1,8,2,9,3,10,4,11,5,12,6,13,7,14"><cfset translated = ""><cfloop index="i" from="1" to="#listLen(evs_q16)#"><cfset translated = listAppend(translated, listGetAt(crossList, listGetAt(evs_q16, i)))></cfloop><cfloop index="i" from="1" to="14"><cfif listFind(translated, i)>"#i#",<cfelse>"",</cfif></cfloop>"#reReplace(EVS_Q16_OTHER, '"', '""', "ALL")#","#EVS_Q17#",<cfloop index="i" from="1" to="6"><cfif listFind(EVS_Q18, i)>"#i#",<cfelse>"",</cfif></cfloop>"#reReplace(EVS_Q18_OTHER, '"', '""', "ALL")#","#EVS_Q19#",<cfloop index="i" from="1" to="6"><cfif listFind(EVS_Q20, i)>"#i#",<cfelse>"",</cfif></cfloop>"#reReplace(EVS_Q20_OTHER, '"', '""', "ALL")#","#EVS_Q21#",<cfloop index="i" from="1" to="4"><cfif listGetAt(EVS_Q22, i) eq "Don't Know">"8",<cfelse>"#listGetAt(EVS_Q22, i)#",</cfif></cfloop>"#reReplace(EVS_FIRSTNAME, '"', '""', "ALL")#","#reReplace(EVS_LASTNAME, '"', '""', "ALL")#","#reReplace(EVS_FIRSTNAME, '"', '""', "ALL")# #reReplace(EVS_LASTNAME, '"', '""', "ALL")#","#EVS_PHONE1#","#EVS_PHONE2#","#reReplace(EVS_ADDRESS, '"', '""', "ALL")#","#reReplace(EVS_CITY, '"', '""', "ALL")#","#reReplace(EVS_ZIP, '"', '""', "ALL")#","#reReplace(EVS_EMAIL, '"', '""', "ALL")#","#dateformat(EVS_DOB, "mm/dd/yyyy")#",<cfif EVS_SSNCODE eq 2>"0000",<cfelseif EVS_SSNCODE eq 3>"9999",<cfelse>"#EVS_SSN#",</cfif>"#reReplace(EVS_ALT1_FIRSTNAME, '"', '""', "ALL")# #reReplace(EVS_ALT1_LASTNAME, '"', '""', "ALL")#","#reReplace(EVS_ALT1_EMAIL, '"', '""', "ALL")#","#reReplace(EVS_ALT1_PHONE, '"', '""', "ALL")#","#reReplace(EVS_ALT1_ADDRESS, '"', '""', "ALL")#","#reReplace(EVS_ALT1_CITY, '"', '""', "ALL")#","#reReplace(EVS_ALT1_ZIP, '"', '""', "ALL")#","#reReplace(EVS_ALT2_FIRSTNAME, '"', '""', "ALL")# #reReplace(EVS_ALT2_LASTNAME, '"', '""', "ALL")#","#reReplace(EVS_ALT2_EMAIL, '"', '""', "ALL")#","#reReplace(EVS_ALT2_PHONE, '"', '""', "ALL")#","#reReplace(EVS_ALT2_ADDRESS, '"', '""', "ALL")#","#reReplace(EVS_ALT2_CITY, '"', '""', "ALL")#","#reReplace(EVS_ALT2_ZIP, '"', '""', "ALL")#","","#reReplace(County, '"', '""', "ALL")#","#reReplace(regionID, '"', '""', "ALL")#","#EVS_SURVEYDATE#"#chr(13)#</cfoutput>
<cfelse>
	Nothing to Export.
</cfif>
